﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Site.Components;
using TransportManager.UI.Pages;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.IO;
using Site.Components.Entity;

namespace Site.Report
{
    public partial class productinoutyearreport : BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                drpYear.GenerateYear(DateTime.Now.Year);
            }
        }

        private DataTable __DataSource
        {
            get
            {
                var ds = SqlHelper.ExecuteDataset(Session["Conn"].ToString(), "Proc_ProductInOutYearReport", drpYear.SelectedValue);
                var dt = ds.Tables[0];
                return dt;
            }
        }

        void BindData()
        {
            rep.DataSource = ProductInOutYearDataTableTransformModel(__DataSource);
            rep.DataBind();
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            BindData();
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            FileStream fstream = new FileStream(MapPath("~/Template/productinoutyeartemplate.xls"), FileMode.Open, FileAccess.Read);
            var hssfworkbook = new HSSFWorkbook(fstream);
            var sheet= hssfworkbook.GetSheetAt(0);


            var list = ProductInOutYearDataTableTransformModel(__DataSource).OrderBy(x => x.Month).ToList();
                         
            int[] index = new int[] { 12, 13, 11, 19, 15, 4, 14, 17, 18, 16, 2, 3, 5 };//设备分类排序,具体参考文档。用反射赋值


            for (int i = 0; i < list.Count; i++)
            {
                var model= list[i];
                var row = sheet.CreateRow(i + 3);
                row.CreateCell(0).SetCellValue(model.Month);
                int current = 0;
                for (int step = 0; step < index.Length; step++)
                {
                    System.Reflection.PropertyInfo propertyin = model.GetType().GetProperty(string.Format("Catalog{0}In", index[step]));
                    current++;
                    row.CreateCell(current).SetCellValue(Convert.ToDouble(propertyin.GetValue(model, null)));
                    current++;
                    System.Reflection.PropertyInfo propertyout = model.GetType().GetProperty(string.Format("Catalog{0}Out", index[step]));
                    row.CreateCell(current).SetCellValue(Convert.ToDouble(propertyout.GetValue(model, null)));
                }
                current++;
                row.CreateCell(current).SetCellValue(model.CatalogIn);
                current++;
                row.CreateCell(current).SetCellValue(model.CatalogOut);
            }

            MemoryStream ms = new MemoryStream();
            hssfworkbook.Write(ms);
         
            fstream.Close();

            string filename = string.Format("材料进出存{0}年报表.xls", drpYear.SelectedValue);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            Response.Clear();
           
            Response.BinaryWrite(ms.GetBuffer());
            Response.End();
        }

        void GenerateData()
        {

        }

        private IList<ProductInOutYearModel> ProductInOutYearDataTableTransformModel(DataTable dt)
        {
            IList<ProductInOutYearModel> list = new List<ProductInOutYearModel>(12);
            for (int j = 0; j < 12; j++)
            {
                list.Add(new ProductInOutYearModel(){ Month=j+1});
            }

            #region "dtm数据转换"
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int thismonth = dr.Field<int>("thismonth"); //取得当前月份

                ProductInOutYearModel model=   list.First(x => x.Month == thismonth );
               
                string cname = dr.Field<string>("cname"); //取得当前分类
                double thisin = dr.Field<double>("thisinmoney"); //入库
                double thisout = dr.Field<double>("thisoutmoney"); //出库
                switch(cname)
                {
                    case "金具":
                        model.Catalog12In += thisin;
                        model.Catalog12Out += thisout;
                        break;
                    case "线材":
                        model.Catalog13In += thisin;
                        model.Catalog13Out += thisout;
                        break;
                    case "塔材及加工件":
                        model.Catalog11In += thisin;
                        model.Catalog11Out += thisout;
                        break;
                    case "钢材":
                        model.Catalog19In += thisin;
                        model.Catalog19Out += thisout;
                        break;
                    case "绝缘材料":
                        model.Catalog15In += thisin;
                        model.Catalog15Out += thisout;
                        break;
                    case "主要设备":
                        model.Catalog4In += thisin;
                        model.Catalog4Out += thisout;
                        break;
                    case "硬母线":
                        model.Catalog14In += thisin;
                        model.Catalog14Out += thisout;
                        break;
                    case "电缆桥支架槽盒":
                        model.Catalog17In += thisin;
                        model.Catalog17Out += thisout;
                        break;
                    case "防火材料":
                        model.Catalog18In += thisin;
                        model.Catalog18Out += thisout;                       
                        break;
                    case "降阻剂":
                        model.Catalog16In += thisin;
                        model.Catalog16Out += thisout;  
                        break;
                    case "耗材":
                        model.Catalog2In += thisin;
                        model.Catalog2Out += thisout; 
                        break;
                    case "工器具":
                        model.Catalog3In += thisin;
                        model.Catalog3Out += thisout; 
                        break;
                    case "汽配":
                        model.Catalog5In += thisin;
                        model.Catalog5Out += thisout; 
                        break;
                }

            }
            #endregion

          
            return list;
        }
    }
}